<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/11/29
 * Time: 9:16
 */

namespace app\storage\model;

use think\Db;
use think\Model;

class ReelsModel extends Model
{
    protected $table = 'iwh_reels';
    protected $pk = 'uuid';
    use \app\storage\util\Common;

    public function Storage()
    {
        return $this->belongsTo('StorageModel', 'storage_uuid', 'uuid')->bind('name');
    }

    public function queryMateriel($condition)
    {
        $where = $condition['where'];
        $pageIndex = $condition['pageIndex'];
        $pageSize = $condition['pageSize'];

        $havingArr = ['reel_count', 'type_count', 'storage_count'];
        $having = $this->getHavingString($havingArr, $where);

        $map = [];
        if (!empty($where['sub_materiel_name'])) {
            $map['sub_materiel_name'] = $where['sub_materiel_name'];
            unset($where['sub_materiel_name']);
        }
        if (!empty($where['shelves_type'])) {
            $map['shelves_type'] = $where['shelves_type'];
            unset($where['shelves_type']);
        }

        if (!empty($where['sub_materiel_id'])) {
            $map['m.sub_materiel_id'] = $where['sub_materiel_id'];
            unset($where['sub_materiel_id']);
        }

        if (!empty($where['min_quantity'])) {
            $map['m.min_quantity'] = $where['min_quantity'];
            unset($where['min_quantity']);
        }

        if (!empty($where['item_id'])) {
            $map['m.item_id'] = $where['item_id'];
            unset($where['item_id']);
        }

        $supplierCount = $where['supplier_count'][1];
        $sType = 'left';
        if (!empty($supplierCount)) {
            $supplierCount = "count like '$supplierCount'" ;
            $sType = 'right';
            unset($where['supplier_count']);
        }

        $storageUuid = $where['storage_uuid'];
        $type = empty($storageUuid) ? 'left' : 'right';

        $where['status'] = 1;
        $baseSql = $this->alias('r')
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->field("sub_materiel_id,count(*) as type_count,sum(count) as reel_count")
            ->group('r.sub_materiel_id')
            ->having($having)
            ->where($where)
            ->select(false);

        if ($storageUuid) {

            $supplierSql = Db::table('iwh_supplier')->alias('s')->field('sub_materiel_id,count')
                ->join(['iwh_supplier_materiel' => 'sm'], 'sm.supplier_id = s.supplier_id')
                ->join(['iwh_supplier_storage' => 't'], 't.supplier_uuid = s.uuid')
                ->where(['storage_uuid' => $storageUuid])
                ->where($supplierCount)
                ->select(false);
        } else {
            $supplierSql = Db::table('iwh_supplier_materiel')->field('sub_materiel_id,count')->where($supplierCount)->select(false);
        }

        $subMaterielModel = new MaterielModel();

        $ret['total'] = $subMaterielModel->alias('m')
            ->join(["($baseSql)" => 'b'], 'b.sub_materiel_id = m.sub_materiel_id', $type)
            ->join(["($supplierSql)" => 'c'], 'c.sub_materiel_id = m.sub_materiel_id', $sType)
            ->having($having)
            ->where($map)
            ->count();

        $ret['row'] = $subMaterielModel->alias('m')
            ->join(["($baseSql)" => 'b'], 'b.sub_materiel_id = m.sub_materiel_id', $type)
            ->join(["($supplierSql)" => 'c'], 'c.sub_materiel_id = m.sub_materiel_id', $sType)
            ->field("shelves_type,m.sub_materiel_id,min_quantity,IFNULL(b.type_count,0) as type_count,IFNULL(b.reel_count,0) as reel_count,m.sub_materiel_name,item_id,IFNULL(c.count,0) as supplier_count")
            ->group('m.sub_materiel_id')
            ->page($pageIndex, $pageSize)
            ->having($having)
            ->where($map)
            ->select();

        return $ret;
    }

    public function queryMaterielDetail($condition)
    {
        $where = $condition['where'];
        $pageIndex = $condition['pageIndex'];
        $pageSize = $condition['pageSize'];
        $where['status'] = 1;
        $day = $where['day'][1];
        if (isset($day)) {
            $day = 0 - intval(str_replace('%', '', $day));
            $time = date("Y-m-d", strtotime("$day day"));
            $where['time'] = ['like', '%' . $time . '%'];
            unset($where['day']);
        }

        $ret['total'] = $this->alias('r')->field('reel_id,seat_code,container_code,count,name,time')
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->order('storage_uuid asc,reel_id asc')
            ->where($where)
            ->count();

        $ret['row'] = $this->alias('r')->field('reel_id,seat_code,container_code,count,name,time,DATEDIFF(now(),time) as day')
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->order('storage_uuid asc,reel_id asc')
            ->page($pageIndex, $pageSize)
            ->where($where)
            ->select();

        return $ret;
    }

    public function queryReels($condition)
    {
        $where = $condition['where'];
        $pageSize = $condition['pageSize'];
        $pageIndex = $condition['pageIndex'];
        $day = $where['day'][1];
        if (isset($day)) {
            $day = 0 - intval(str_replace('%', '', $day));
            $time = date("Y-m-d", strtotime("$day day"));
            $where['time'] = ['like', '%' . $time . '%'];
            unset($where['day']);
        }

        $where['status'] = 1;
        $ret['total'] = $this->alias('r')->field("r.*,s.name")
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->order('storage_uuid asc,time asc')
            ->where($where)
            ->count();

        $ret['row'] = $this->alias('r')->field("r.*,s.name,DATEDIFF(now(),time) as day")
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->page($pageIndex, $pageSize)
            ->order('storage_uuid asc,time asc')
            ->where($where)
            ->select();

        return $ret;
    }


}